﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Generic;

/// <summary>
/// DBClass 的摘要描述
/// </summary>
/// 
public enum TableType
{
    Table, View
}

public class DBClass
{
    static string connString = ConfigurationManager.ConnectionStrings["MasterDB"].ConnectionString;
    //static SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MasterDB"].ConnectionString);

    public DBClass()
    {

    }

    public static DataSet getTables()
    {

        return getINFORMATION_SCHEMA_TABLES(TableType.Table);
    }

    public static DataSet getSingleTable(string tableName)
    {
        string strSql = string.Format("select P.*,Q.value AS Descriptions ,(Case IS_NULLABLE When 'NO' Then 'N' ELSE 'Y' END) as NULLABLE FROM (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='{0}' ) as P LEFT OUTER JOIN (SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{0}', 'column', default) ) as Q ON P.COLUMN_NAME = Q.objname collate Chinese_Taiwan_Stroke_CI_AS", tableName);
        DataSet ds = new DataSet();
        SqlConnection myConnection = new SqlConnection(connString);
        using (myConnection)
        { 
            myConnection.Open();
            SqlCommand myCommand = new SqlCommand(strSql, myConnection);
            myCommand.CommandType = CommandType.Text;
           

            #region 撈主要資料

            SqlDataAdapter da = new SqlDataAdapter(myCommand);
            da.Fill(ds, "main");

            #endregion

            #region 撈主鍵
            //系統檢視表 或 系統預存程序 都可以執行

            //系統檢視表
            //strSql = string.Format("select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE Where TABLE_NAME = '{0}'", ddlTableName.SelectedItem);
            //myCommand.CommandText = strSql;

            //系統預存程序
            SqlCommand comm = new SqlCommand("sys.sp_pkeys", myConnection);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("table_name", tableName);


            SqlDataAdapter daPK = new SqlDataAdapter(comm);
            DataSet dspk = new DataSet();

            daPK.Fill(dspk, "pk");
            #endregion

            #region 整理資料
            List<object> pklist = new List<object>();

            foreach (DataRow dr in dspk.Tables["pk"].Rows)
            {
                pklist.Add(dr["COLUMN_NAME"]);
            }

            ds.Tables["main"].Columns.Add("pk");

            foreach (DataRow dr in ds.Tables["main"].Rows)
            {
                try
                {
                    if (pklist.Remove(dr["COLUMN_NAME"]))
                        dr["pk"] = "*";

                }
                catch { }
            }
            #endregion


            myConnection.Close();
        }

        return ds;
    }

    public static DataSet getINFORMATION_SCHEMA_TABLES(TableType type)
    {
        DataSet ds = new DataSet();
        string strSql = string.Format("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE like'%{0}') and(TABLE_NAME!='dtproperties') order by TABLE_NAME", (TableType)Enum.Parse(typeof(TableType), type.ToString()));
        SqlConnection myConnection = new SqlConnection(connString);
        using (myConnection)
        {
            myConnection.Open(); 
            SqlCommand myCommand = new SqlCommand(strSql, myConnection);
            myCommand.CommandType = CommandType.Text;
            
            SqlDataAdapter da = new SqlDataAdapter(myCommand);
            da.Fill(ds);

            myConnection.Close();
        }
        return ds;
    }

    //public static DataSet getViewList()
    //{

    //}

    public static StoredProcedureCollection getStoredProceduresList()
    {
        ServerConnection conn = new ServerConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["MasterDB"].ConnectionString;
        Server server = new Server(conn);


        Database db = server.Databases["Northwind"];

        StoredProcedureCollection spc = db.StoredProcedures;

        conn.Disconnect();
        return spc;
    }

    public static DataSet getStoredProceduresTable()
    {
        SqlConnection myConnection = new SqlConnection(connString);
        DataSet ds = new DataSet();
        using (myConnection)
        {
            myConnection.Open();
            string strSql = " select * from sys.procedures Where substring( [name],1,3) not in('sp_') and is_ms_shipped='0' ";
            SqlCommand myCommand = new SqlCommand(strSql, myConnection);
            SqlDataAdapter da = new SqlDataAdapter(myCommand);

            da.Fill(ds);
            myConnection.Close();

        }
        return ds;
    }



}
